package com.itg.vms.dao.impl;

import com.itg.vms.dao.UserDao;
import com.itg.vms.entity.User;
import com.itg.vms.util.C3P0Utils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * 用户表数据访问实现
 *
 * @author itg
 * @create 2023年03月04日 11:48:29
 */
public class UserDaoImpl implements UserDao {

    QueryRunner queryRunner = new QueryRunner(C3P0Utils.getDataSource());

    /**
     * 插入用户
     *
     * @param user 要插入的用户实体
     * @return 是否添加成功
     */
    @Override
    public boolean insertUser(User user) {
        String sql = "insert into vms.sys_user " + "(user_name, nick_name, user_type, email, phonenumber, gender, avatar, password, status, del_flag," + " login_date, create_by, create_time, update_by, update_time, remark) " + "values (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);";

        try {
            queryRunner.update(sql, user.getUserName(), user.getNickName());
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }

    /**
     * 通过用户名和密码查询用户（登录）
     *
     * @param username 用户名
     * @param password 密码
     * @return 用户实体
     */
    @Override
    public User selectUserByUsernameAndPassword(String username, String password) {
        String sql = "select user_name as userName,nick_name as nickName,user_id as userId,user_type as userType from vms.sys_user where user_name = ? and password = ?";
        User user = null;
        try {
            user = queryRunner.query(sql, new BeanHandler<User>(User.class), username, password);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 添加用户信息
     */
    @Override
    public boolean addUser(User user) {
        String sql = "insert into vms.sys_user (del_flag,create_time,create_by,user_name,nick_name,user_type,email,phonenumber,gender,password,status) values(0,sysdate(),?,?,?,?,?,?,?,?,?)";
        int i = 0;
        try {
            i = queryRunner.update(sql, user.getCreateBy(), user.getUserName(), user.getNickName(), user.getUserType(), user.getEmail(), user.getPhoneNumber(), user.getGender(), user.getPassword(), user.getStatus());

        } catch (SQLException e) {
            e.printStackTrace();
        }

        return i > 0;
    }


    /**
     * 删除用户
     *
     * @param user_id  要删除的用户信息
     * @param updateBy 删除操作的用户
     * @return 影响的行数
     */
    @Override
    public int deleteUserById(String user_id, String updateBy) {
        //String sql ="delete from vms.sys_user where user_id=?";
        String sql = "update vms.sys_user set del_flag = 1,update_by = ?,update_time =sysdate() where user_id = ?";
        int i = 0;
        try {
            i = queryRunner.update(sql, updateBy, user_id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i;
    }

    /**
     * 分页查询用户
     *
     * @param currentPage 当前页面
     * @param pageSize    页面显示的数据条数
     * @return 用户列表
     * @author zjzaki
     */
    @Override
    public List<User> selectAllUser(Integer currentPage, Integer pageSize) {

        // 仅展示用户id 用户名 用户昵称 用户类型 手机 用户状态 创建时间
        String sql = "select user_id as userId, user_name as userName, nick_name as nickName, user_type as userType" + ", phonenumber as phoneNumber, status, create_time as createTime" + " from vms.sys_user where del_flag = 0 limit ?,?";
        //定义用户列表
        List<User> query = null;
        try {
            query = queryRunner.query(sql, new BeanListHandler<>(User.class), currentPage, pageSize);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return query;
    }

    /**
     * 得到user的总记录数
     *
     * @return user表的总记录数
     * @author zjzaki
     */
    @Override
    public Long getAllUserSize() {

        String sql = "select COUNT(user_id) as sum from vms.sys_user where del_flag = 0";
        Long sum = 0L;
        try {
            sum = (Long) queryRunner.query(sql, new ScalarHandler("sum"));
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return sum;
    }

    /**
     * 修改用户相关信息
     *
     * @param user_name
     * @param nick_name
     * @param user_type
     * @param email
     * @param phonenumber
     * @param gender
     * @param status
     * @return
     */
    @Override
    public boolean UpdateUserById(String user_name, String nick_name, String user_type, String email, String phonenumber, String gender, String status, String username) {
        String sql = "update vms.sys_user set nick_name= ?, user_type= ?,email= ?,phonenumber= ?,gender= ?," + "status=?,update_by=?,update_time =sysdate() where user_name = ?";
        int i = 0;
        try {
            i = queryRunner.update(sql, nick_name, user_type, email, phonenumber, gender, status, username, user_name);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        System.out.println(i);
        return i > 0;
    }

    /**
     * 修改密码
     *
     * @param new_password
     * @param username
     * @return
     */
    @Override
    public boolean updatePassword(String new_password, String username) {
        String sql = "update vms.sys_user set password = ? where user_name = ?";
        int i = 0;
        try {
            i = queryRunner.update(sql, new_password, username);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return i > 0;
    }

    /**
     * 注册用户
     *
     * @param user
     * @return
     */
    @Override
    public boolean registerUser(User user) {
        String sql = "insert into vms.sys_user (user_name,password, nick_name, email, phonenumber,user_type,del_flag,create_time) values (?,?,?,?,?,?,?,sysdate())";
        int i = 0;
        try {
            i = queryRunner.update(sql, user.getUserName(), user.getPassword(), user.getNickName(), user.getEmail(), user.getPhoneNumber(), "1", "0");
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return i > 0;
    }

    /**
     * 根据用户名查询用户
     *
     * @param user_id
     * @param user_name
     * @param nick_name
     * @param phonenumber
     * @return
     */
    @Override
    public List<User> selectUserByName(String user_id, String user_name, String nick_name, String phonenumber) {
        List<User> list = new ArrayList<>();
        //1) 准备SQL语句和参数
        StringBuilder sb = new StringBuilder();
        List<Object> pars = new ArrayList<>();
        // 仅展示用户id 用户名 用户昵称 用户类型 手机 用户状态 创建时间
        sb.append("select user_id as userId, user_name as userName, nick_name as nickName, user_type as userType , phonenumber as phoneNumber, status, create_time as createTime from vms.sys_user where del_flag=0 ");

        if (user_id != null && !"".equals(user_id)) {
            sb.append("and user_id = ?");
            pars.add(user_id);
        }
        if (user_name != null && !"".equals(user_name)) {
            sb.append("and user_name like ?");
            pars.add("%" + user_name + "%");
        }
        if (nick_name != null && !"".equals(nick_name)) {
            sb.append("and nick_name like ?");
            pars.add("%" + nick_name + "%");
        }
        if (phonenumber != null && !"".equals(phonenumber)) {
            sb.append("and phonenumber like ?");
            pars.add("%" + phonenumber + "%");
        }

        try {
            list = queryRunner.query(sb.toString(), new BeanListHandler<>(User.class), pars.toArray());
        } catch (Exception e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * 获取编辑框中的数据
     *
     * @param user_id
     * @return
     */
    @Override
    public User GeteditUserById(String user_id) {
        String sql = "select user_name as userName,nick_name as nickName, user_type as userType,email,phonenumber,gender,status from vms.sys_user where user_id = ?";
        User user = null;
        try {
            user = queryRunner.query(sql, new BeanHandler<>(User.class), user_id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return user;
    }

    /**
     * 根据用户id修改用户状态
     *
     * @param userId 用户id
     * @param status 用户状态
     * @return 影响的行数
     */
    @Override
    public int updateUserStatus(String userId, String status) {
        String sql = "update vms.sys_user set status = ? where user_id =?";
        int update = 0;
        try {
            update = queryRunner.update(sql, status, userId);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return update;
    }


    @Override
    public boolean updateAdminAndUserInfo(User user) {
        String sql = "update vms.sys_user set nick_name = ?,phonenumber = ?,email = ?,remark = ? where user_name = ? ";
        int i = 0;
        try {
            i = queryRunner.update(sql, user.getNickName(), user.getPhoneNumber(), user.getEmail(), user.getRemark(), user.getUserName());
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return i > 0;
    }

    /**
     * 通过用户名查询用户
     *
     * @param username 用户名
     * @return 查询到的用户实体
     */
    @Override
    public User selectUserByUsername(String username) {
        String sql = "select user_name as userName,nick_name as nickName,user_id as userId,user_type as userType from vms.sys_user where user_name = ?";

        User user = null;
        try{
            user = queryRunner.query(sql, new BeanHandler<>(User.class), username);
        }catch (Exception e){
            e.printStackTrace();
        }
        return user;
    }
}
